package com.sams.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class TestPOI {

	public static void main(String[] args) throws Exception {
//		/
//		  HSSFWorkbook workbook = new HSSFWorkbook();
//		  
//		  HSSFCellStyle style = workbook.createCellStyle();
//		  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//		  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//		  
//		  HSSFSheet sheet = workbook.createSheet("sheet");
//		  
//		  HSSFRow row0 = sheet.createRow(0); HSSFCell cell_00 = row0.createCell(0);
//		  cell_00.setCellStyle(style); cell_00.setCellValue("日期"); HSSFCell cell_01 =
//		  row0.createCell(1); cell_01.setCellStyle(style); cell_01.setCellValue("午别");
//		  
//		  HSSFRow row1 = sheet.createRow(1); HSSFCell cell_10 = row1.createCell(0);
//		  cell_10.setCellStyle(style); cell_10.setCellValue("20180412"); HSSFCell
//		  cell_11 = row1.createCell(1); cell_11.setCellStyle(style);
//		  cell_11.setCellValue("上午");
//		  
//		  HSSFRow row2 = sheet.createRow(2); HSSFCell cell_21 = row2.createCell(1);
//		  cell_21.setCellStyle(style); cell_21.setCellValue("下午");
		  
		  // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列) // 行和列都是从0开始计数，且起始结束都会合并 //
		  //这里是合并excel中日期的两行为一行 
//		  CellRangeAddress region = new CellRangeAddress(1, 2, 0,
//		  0); sheet.addMergedRegion(region);
//		  
//
		// 第一步，创建一个workbook，对应一个Excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();

		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet hssfSheet = workbook.createSheet("归档数据");


		String[] titles_1 = { "序号", "受助项目(详见说明)", "学校(幼儿园)名称", "受助学生信息", "受助原因", "家庭住址(填写户口簿住址)", "家长(监护人)信息", "账户信息" };
		HSSFRow row = hssfSheet.createRow(0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
		// 居中样式
		hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont font = workbook.createFont();
		font.setFontName("黑体");
		// font.setFontHeightInPoints((short) 16);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		hssfCellStyle.setFont(font);// 选择需要用到的字体格式

		HSSFCell hssfCell = null;
 	 
		for (int i = 0; i < 4; i++) {
			hssfCell = row.createCell(i);// 列索引从0开始
			hssfCell.setCellValue(titles_1[i]);// 列名1
			hssfCell.setCellStyle(hssfCellStyle);// 列居中显示 '
		}
		  CellRangeAddress region = new CellRangeAddress(0, 1, 0, 0);
		  hssfSheet.addMergedRegion(region);
		  CellRangeAddress region2 = new CellRangeAddress(0, 1, 1, 1);
		  hssfSheet.addMergedRegion(region2);
		  CellRangeAddress region3 = new CellRangeAddress(0, 1, 2, 2);
		  hssfSheet.addMergedRegion(region3);
		  CellRangeAddress region4 = new CellRangeAddress(0, 0, 3, 8);
		  hssfSheet.addMergedRegion(region4);
		
		    hssfCell = row.createCell(9);// 列索引从0开始
			hssfCell.setCellValue(titles_1[4]);// 列名1
			hssfCell.setCellStyle(hssfCellStyle);
		     CellRangeAddress region5 = new CellRangeAddress(0, 0, 9, 10);
			 hssfSheet.addMergedRegion(region5);
			 
			  hssfCell = row.createCell(11); 
			  hssfCell.setCellValue(titles_1[5]);// 列名1
			  hssfCell.setCellStyle(hssfCellStyle);
			  CellRangeAddress region6 = new CellRangeAddress(0, 0, 11, 16);
				 hssfSheet.addMergedRegion(region6);
				 
				  hssfCell = row.createCell(17); 
				  hssfCell.setCellValue(titles_1[6]);// 列名1
				  hssfCell.setCellStyle(hssfCellStyle);
				  CellRangeAddress region7 = new CellRangeAddress(0, 0, 17, 20);
					 hssfSheet.addMergedRegion(region7);
					 
					  hssfCell = row.createCell(21); 
					  hssfCell.setCellValue(titles_1[7]);// 列名1
					  hssfCell.setCellStyle(hssfCellStyle);
					  CellRangeAddress region8 = new CellRangeAddress(0, 0, 21, 23);
						 hssfSheet.addMergedRegion(region8);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short

		HSSFRow row2 = hssfSheet.createRow(1);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle hssfCellStyle2 = workbook.createCellStyle();

		// 居中样式
		hssfCellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFFont font2 = workbook.createFont();
		font2.setFontName("黑体");
		// font.setFontHeightInPoints((short) 16);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		hssfCellStyle2.setFont(font2);// 选择需要用到的字体格式

		String[] titles_2 = { "序号", "受助项目", "学校", "姓名", "性别", "年级", "班级", "身份证号", "学籍号", "受助类别", "详细原因", "省", "市", "县",
				"镇", "村(社区、街道)", "组(路)", "家长地址", "家长姓名", "与学生关系", "联系电话", "银行卡或存折号", "银行名称", "资助金额或免学费金额(元" };
	
		HSSFCell hssfCell2 = null;
		for (int i = 0; i < titles_2.length; i++) {
			hssfCell2 = row2.createCell(i);// 列索引从0开始
			hssfCell2.setCellValue(titles_2[i]);// 列名1
			hssfCell2.setCellStyle(hssfCellStyle2);// 列居中显示 '

		}

		File file = new File("E:\\demo.xls");
		FileOutputStream fout = new FileOutputStream(file);
		workbook.write(fout);
		fout.close();

	}

}
